create table message(
	message_no number not null primary key,
	message_title varchar2(50) not null,
	message_content varchar2(450) not null,
	message_writedate varchar2(30) not null,
	message_writer varchar2(30) not null,
	message_receiver varchar2(30) not null,
	receive number,
	send number,
	relevel number not null,
	foreign key (message_writer) references member(member_id)
)
drop table message
select * from message
select	message_no_seq.nextval
		from    dual

create sequence message_no_seq nocache;
drop sequence message_no_seq;

create table member(
	member_id varchar2(30) primary key,
	member_password varchar2(30) not null,
	member_name varchar2(12) not null,
	member_level number(1) not null,
	member_birthday number not null,
	member_address varchar2(100) not null,
)


select * from MEMBER
insert into "YOGIRO"."MEMBER" ("MEMBER_ID", "MEMBER_PASSWORD", "MEMBER_NAME", "MEMBER_LEVEL", "MEMBER_BIRTHDAY", "MEMBER_ADDRESS") values('i-1', '1111', '홍길동', 0, 19881010, '경기도 성남시 분당구')
insert into "YOGIRO"."MEMBER" ("MEMBER_ID", "MEMBER_PASSWORD", "MEMBER_NAME", "MEMBER_LEVEL", "MEMBER_BIRTHDAY", "MEMBER_ADDRESS") values('i-2', '2222', '박시후', 0, 19881111, '경기도 성남시 서현동'
insert into "YOGIRO"."MEMBER" ("MEMBER_ID", "MEMBER_PASSWORD", "MEMBER_NAME", "MEMBER_LEVEL", "MEMBER_BIRTHDAY", "MEMBER_ADDRESS") values('Alice', '0000', 'Alice', 0, 19881212, '경기도 성남시 분당구 정자동')

 select  member_id as memberId
		from	member
where member_level=0

 select  message_no as messageNo,
				message_title as messageTitle,
				message_content as messageContent,
				message_writedate as messageWritedate,
				message_writer as messageWriter,
				message_receiver as messageReceiver,
				relevel 
		from(select ceil(rownum/5)page,message_no,message_title,message_content,message_writedate,message_writer,message_receiver,relevel
		from(select message_no,message_title,message_content,message_writedate,message_writer,message_receiver,relevel from message where relevel=1 and message_receiver='i-1' and receive=0 order by message_no desc))
		where page=1